﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using OF.Lib.DataAccess.Entity;
using System.Reflection;
using OF.Lib.Utility;

namespace OF.Lib.DataAccess.Core
{
    public class SqlBuilderSqlServerImpl : SqlBuilderBase, ISqlBuilder
    {
        public string BuildPagedQuerySql(SQL sqlCfg, QueryFilter filter)
        {
            base.Init_PagedQuery(sqlCfg);
            string strWhere = base.BuildWhereSql(PagedQuery_4_WhereSection, filter);
            //SELECT TOP PAGESIZE *
            //FROM
            //    (
            //        SELECT  *
            // ,ROW_NUMBER() OVER(ORDER BY id) AS RowNumber
            // FROM table1
            //    ) as A
            //WHERE RowNumber > PAGESIZE * PAGEINDEX

            StringBuilder sbSql = new StringBuilder();
            sbSql.AppendLine("SELECT COUNT(1) AS TotalCount ");
            sbSql.AppendFormat("FROM {0}", PagedQuery_2_TableSection);
            sbSql.AppendLine(PagedQuery_3_Part);
            sbSql.AppendFormat(strWhere);
            sbSql.AppendLine(PagedQuery_5_Part);
            sbSql.AppendLine();

            sbSql.AppendFormat("SELECT TOP {0} * FROM ", filter.PageSize);
            sbSql.AppendLine();
            sbSql.AppendLine("(");
            sbSql.AppendLine(PagedQuery_1_SelectSection);
            if (string.IsNullOrWhiteSpace(filter.SortFields))
            {
                sbSql.AppendFormat(" ,ROW_NUMBER() OVER({0}) AS RowNumber ", PagedQuery_6_SortSection);
            }
            else
            {
                sbSql.AppendFormat(" ,ROW_NUMBER() OVER(ORDER BY {0}) AS RowNumber ", filter.SortFields);
            }
            sbSql.AppendLine();
            sbSql.AppendFormat("FROM {0}", PagedQuery_2_TableSection);
            sbSql.AppendLine(PagedQuery_3_Part);

            sbSql.AppendFormat(strWhere);

            sbSql.AppendLine(PagedQuery_5_Part);
            sbSql.AppendLine(PagedQuery_7_FootPart);
            sbSql.AppendLine(") AS T_RN");
            sbSql.AppendFormat("WHERE RowNumber>{0}", filter.PageSize * filter.PageIndex);

            return sbSql.ToString();
        }

        public string BuildSO_InsertSql<T>(string dbName, object dataParameter, string excludeProperties = null) where T : class, new()
        {
            string sql = BuildSOInsertSql<T>(dbName, dataParameter, excludeProperties);
            return sql;
        }

        public string BuildSO_InsertSqlWithReturnPK<T>(string dbName, object dataParameter, string excludeProperties = null) where T : class, new()
        {
            string sql = BuildSOInsertSql<T>(dbName, dataParameter, excludeProperties);
            sql += Environment.NewLine;
            sql += "SELECT SCOPE_IDENTITY() as pk_id;";
            return sql;
        }

        private string BuildSOInsertSql<T>(string dbName, object dataParameter, string excludeProperties = null) where T : class, new()
        {
            string sql = "INSERT INTO {0}({1}) VALUES ({2});";
            string table = string.Empty;                        //表名
            List<string> member = new List<string>();           //字段列表
            List<string> member_param = new List<string>();     //字段变量列表
            Type type = dataParameter.GetType();

            table = typeof(T).Name;

            List<string> excludeList = new List<string>();
            if (!string.IsNullOrWhiteSpace(excludeProperties))
            {
                excludeList = excludeProperties.Trim().ToUpper().Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList<string>();
            }

            foreach (PropertyInfo item in type.GetProperties())
            {
                if (excludeList.Contains(item.Name.ToUpper()))
                {
                    continue;
                }

                if (item.PropertyType.Name.StartsWith("List") || item.PropertyType.Name.StartsWith("Dictionary"))
                {
                    continue;
                }
                object value = item.GetValue(dataParameter, null);
                if (value != null)
                {
                    string name = item.Name;
                    if (!member.Exists(f => f.Trim().ToUpper() == name.Trim().ToUpper()))
                    {
                        member.Add(name);
                        member_param.Add("@" + name);
                    }
                }

            }
            sql = string.Format(sql, dbName + ".dbo." + table, string.Join(",", member), string.Join(",", member_param));
            return sql;
        }


        public string BuildSO_Load(string dbName, string table, string strWhere)
        {
            return string.Format("SELECT TOP(1) * FROM {0}.dbo.{1} WITH(NOLOCK) WHERE {2}", dbName, table, strWhere);
        }
        public string BuildSO_GetList(int? top, string dbName, string table, string strWhere, string orderBy)
        {
            string sqlTemplate = string.Empty;
            if (top.HasValue && top.Value > 0)
            {
                sqlTemplate = "SELECT TOP(" + top.ToString() + ") * FROM {0}.dbo.{1} WITH(NOLOCK) WHERE {2} {3}";
            }
            else
            {
                sqlTemplate = "SELECT * FROM {0}.dbo.{1} WITH(NOLOCK) WHERE {2} {3}";
            }
            if (!string.IsNullOrWhiteSpace(orderBy))
            {
                orderBy = " ORDER BY " + orderBy;
            }

            return string.Format(sqlTemplate, dbName, table, strWhere, orderBy);
        }

        public string BuildSO_Update(string dbName, string table, object dataParameter, string excludeProperties, string strWhere, ref dynamic paramObject)
        {
            //UPDATE TestDB.dbo.Customer SET Name=@Name,Memo=@Memo WHERE SysNo=@SysNo
            string sql = "UPDATE {0} SET {1} WHERE {2};"; 
            List<string> memberSet = new List<string>();           //字段赋值列表
            Type type = dataParameter.GetType();
          

            List<string> excludeList = new List<string>();
            if (!string.IsNullOrWhiteSpace(excludeProperties))
            {
                excludeList = excludeProperties.Trim().ToUpper().Split(",".ToCharArray(), StringSplitOptions.RemoveEmptyEntries).ToList<string>();
            }

            IDictionary<string, object> dicParam = (IDictionary<string, object>)paramObject;

            foreach (PropertyInfo item in type.GetProperties())
            {
                if (excludeList.Contains(item.Name.ToUpper()))
                {
                    continue;
                }

                if (item.PropertyType.Name.StartsWith("List") || item.PropertyType.Name.StartsWith("Dictionary"))
                {
                    continue;
                }

                object value = item.GetValue(dataParameter, null);
                string name = item.Name;
                string valueFeild = "Update_ValueFeild_" + name;
                string fieldset = name + "=@" + valueFeild;
                if (!memberSet.Exists(f => f.Trim().ToUpper() == fieldset.Trim().ToUpper()))
                {
                    memberSet.Add(fieldset);
                }

               
                if(!dicParam.ContainsKey(valueFeild))
                {
                    dicParam.Add(valueFeild, value);
                }
                 
            }

            paramObject = dicParam;
            sql = string.Format(sql, dbName + ".dbo." + table, string.Join(",", memberSet), strWhere);
            return sql;


        }

        public string BuildSO_Delete(string dbName, string table, string strWhere)
        {
            return string.Format("DELETE FROM {0}.dbo.{1} WHERE {2}", dbName, table, strWhere);
        }
    }


}
